package cmd

import (
	"bytes"
	"database/sql"
	"fmt"
	"regexp"
	"strconv"
	"strings"
	"time"
)

var tabRet []string
var tableCount int
var failedCount int

type Database interface {
	// TableCreate (logDir string, tableMap map[string][]string) (result []string) 单线程
	TableCheck(logDir string, tblName string) string                     //检查目标表是否存在
	TableCreate(logDir string, tblName string, ch chan struct{})         //step 1:建表
	IdxCreate(logDir string, tableName string, ch chan struct{}, id int) //step 2:建主键
	SeqCreate(logDir string) (ret []string)                              //step 3:建自增
	FkCreate(logDir string) (ret []string)                               //step 4:建外键
	NormalIdx(logDir string) (ret []string)                              //step 5:建普通索引
	CommentCreate(logDir string) (ret []string)                          //step 6:补充注释
	ViewCreate(logDir string) (ret []string)                             //step 7:打印视图
	PrintDbFunc(logDir string)                                           //step 8:转存存储过程
}

type Table struct {
	columnName             string
	dataType               string
	characterMaximumLength sql.NullString
	isNullable             string
	columnDefault          string
	numericPrecision       int
	numericScale           int
	datetimePrecision      string
	columnKey              string
	columnComment          string
	ordinalPosition        int
	avgColLen              int
	destType               string
	destNullable           string
	destDefault            string
	autoIncrement          int
	destSeqSql             string
	destDefaultSeq         string
	dropSeqSql             string
	destIdxSql             string
	viewSql                string
}

func (tb *Table) TableCheck(logDir string, tblName string) string {
	checkSql := fmt.Sprintf("SHOW TABLES LIKE '%s'", tblName)
	var result string
	err := destDb.QueryRow(checkSql).Scan(&result)
	if err != nil {
		log.Error(err)
	}
	return result
}

// step 1:
// 建表
func (tb *Table) TableCreate(logDir string, tblName string, ch chan struct{}) {
	defer wg2.Done()
	var newTable Table //源表
	var colDefaultValue sql.NullString
	tableCount += 1
	// 使用goroutine并发的创建多个表
	var colTotal int
	if selFromYml { //-s自定义迁移表的时候，统一把yml文件的表名转为大写(否则查询语句的表名都是小写)，原因是map键值对(key:value)，viper这个库始终把key的值转为小写的值
		tblName = strings.ToUpper(tblName)
	}
	createTblSql := "create table " + fmt.Sprintf("`") + tblName + fmt.Sprintf("`") + "("
	// 查询当前表总共有多少个列字段
	//-- 假设您的表在 dbo 架构下，如果不在请修改此处
	var userName string
	srcSchemaSql := `SELECT 
    s.name AS schema_name
FROM 
    sys.schemas s
    INNER JOIN sys.sysusers u ON u.uid = s.principal_id
WHERE 
    u.name = USER_NAME() and s.name NOT IN ('db_owner', 'db_accessadmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_ddladmin', 'db_denydatareader', 'db_denydatawriter', 'db_securityadmin', 'guest', 'INFORMATION_SCHEMA', 'sys')
`
	//err := srcDb.QueryRow("select current_user").Scan(&userName)
	err := srcDb.QueryRow(srcSchemaSql).Scan(&userName)
	if err != nil {
		log.Error(srcSchemaSql, err)
	}
	//防止上面是空拿不到再次确认一下
	if userName == "" {
		srcSchemaSql = "SELECT SCHEMA_NAME() AS CurrentSchema"
		srcDb.QueryRow(srcSchemaSql).Scan(&userName)
	}
	srcColumnSql := `
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '%s'
  AND TABLE_SCHEMA = '%s';
`
	colTotalSql := fmt.Sprintf(srcColumnSql, tblName, userName)
	err = srcDb.QueryRow(colTotalSql).Scan(&colTotal)
	if err != nil {
		log.Error(err)
	}
	if colTotal == 0 {
		log.Error("Table ", tblName, " not exist and not create table")
		return
	}
	// 查询源库表结构
	//-- SQL Server 无法直接获取列的描述信息，暂时设为'null'
	//-- SQL Server 中没有 AVG_COL_LEN，将其设为 -1
	//	srcTableSql := `SELECT
	//    A.COLUMN_NAME,
	//    A.DATA_TYPE,
	//    A.CHARACTER_MAXIMUM_LENGTH AS CHAR_LENGTH,
	//    CASE WHEN A.IS_NULLABLE = 'YES' THEN 'YES' ELSE 'NO' END AS isnull,
	//    A.COLUMN_DEFAULT AS DATA_DEFAULT,
	//    CASE WHEN A.NUMERIC_PRECISION IS NULL THEN -1 ELSE A.NUMERIC_PRECISION END AS DATA_PRECISION,
	//    CASE
	//        WHEN A.NUMERIC_SCALE IS NULL THEN -1
	//        WHEN A.NUMERIC_SCALE > 30 THEN IIF(A.NUMERIC_PRECISION > 30, 29, A.NUMERIC_SCALE)
	//        ELSE A.NUMERIC_SCALE
	//    END AS DATA_SCALE,
	//    'null' AS COMMENTS,
	//    -1 AS AVG_COL_LEN,
	//    A.ORDINAL_POSITION AS COLUMN_ID
	//FROM INFORMATION_SCHEMA.COLUMNS A
	//WHERE A.TABLE_NAME = '%s'
	//ORDER BY COLUMN_ID`
	srcTableSql := `
SELECT 
    A.COLUMN_NAME,
    A.DATA_TYPE,
    A.CHARACTER_MAXIMUM_LENGTH AS CHAR_LENGTH,
    CASE WHEN A.IS_NULLABLE = 'YES' THEN 'YES' ELSE 'NO' END AS isnull,
    A.COLUMN_DEFAULT AS DATA_DEFAULT,
    CASE WHEN A.NUMERIC_PRECISION IS NULL THEN -1 ELSE A.NUMERIC_PRECISION END AS DATA_PRECISION,
    CASE
        WHEN A.NUMERIC_SCALE IS NULL THEN -1
        WHEN A.NUMERIC_SCALE > 30 THEN 
            CASE 
                WHEN A.NUMERIC_PRECISION > 30 THEN 29
                ELSE A.NUMERIC_SCALE
            END
        ELSE A.NUMERIC_SCALE
    END AS DATA_SCALE,
    'null' AS COMMENTS,  
    -1 AS AVG_COL_LEN,   
    A.ORDINAL_POSITION AS COLUMN_ID
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE A.TABLE_NAME = '%s'
ORDER BY COLUMN_ID
`
	//sqlStr := fmt.Sprintf("SELECT A.COLUMN_NAME,A.DATA_TYPE,A.CHAR_LENGTH,case when A.NULLABLE ='Y' THEN 'YES' ELSE 'NO' END as isnull,A.DATA_DEFAULT,case when A.DATA_PRECISION is null then -1 else  A.DATA_PRECISION end DATA_PRECISION,case when A.DATA_SCALE is null then -1 when A.DATA_SCALE >30 then least(A.DATA_PRECISION,30)-1 else  A.DATA_SCALE end DATA_SCALE, nvl(B.COMMENTS,'null') COMMENTS,case when a.AVG_COL_LEN is null then -1 else a.AVG_COL_LEN end AVG_COL_LEN,COLUMN_ID FROM USER_TAB_COLUMNS A LEFT JOIN USER_COL_COMMENTS B ON A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME WHERE A.TABLE_NAME='%s' ORDER BY COLUMN_ID", tblName)
	//fmt.Println(sqlStr)
	sqlStr := fmt.Sprintf(srcTableSql, tblName)
	rows, err := srcDb.Query(sqlStr)
	if err != nil {
		log.Error(sqlStr, err)
	}
	// 遍历PostgreSql表字段,一行就是一个字段的基本信息
	for rows.Next() {
		if err := rows.Scan(&newTable.columnName, &newTable.dataType, &newTable.characterMaximumLength, &newTable.isNullable, &colDefaultValue, &newTable.numericPrecision, &newTable.numericScale, &newTable.columnComment, &newTable.avgColLen, &newTable.ordinalPosition); err != nil {
			log.Error(err)
		}
		// 判断colDefaultValue的长度，如果len大于0说明就是有非null的默认值，如果len为0说明在源库的默认值就是null
		if colDefaultValue.Valid && len([]rune(colDefaultValue.String)) > 0 {
			newTable.columnDefault = colDefaultValue.String
		} else {
			newTable.columnDefault = "null"
		}
		//fmt.Println(columnName,dataType,characterMaximumLength,isNullable,columnDefault,numericPrecision,numericScale,datetimePrecision,columnKey,columnComment,ordinalPosition)
		// 列字段是否允许null
		switch newTable.isNullable {
		case "NO":
			newTable.destNullable = "not null"
		default:
			newTable.destNullable = "" // 原先是"null"
		}
		// 列字段default默认值的处理
		switch {
		case newTable.columnDefault != "null" && newTable.columnDefault != "NULL": // 默认值不为空
			//字符串类型
			upperDefault := strings.ToUpper(newTable.columnDefault)

			if newTable.dataType == "varchar" || newTable.dataType == "char" {
				if upperDefault == "(GETDATE())" || upperDefault == "(SYSDATETIME())" || upperDefault == "(USER)" { // PostgreSql默认值不支持函数，统一设为null
					newTable.destDefault = "default null"
				} else {
					newTable.destDefault = "default " + strings.ReplaceAll(strings.ReplaceAll(newTable.columnDefault, "(", ""), ")", "") //去掉默认值中的左右括号，如( 'user' )
				}
			} else if newTable.dataType == "nvarchar" || newTable.dataType == "nchar" {
				if upperDefault == "(GETDATE())" || upperDefault == "(SYSDATETIME())" || upperDefault == "(USER)" { // PostgreSql默认值不支持函数，统一设为null
					newTable.destDefault = "default null"
				} else {
					newTable.destDefault = "default " + strings.ReplaceAll(strings.ReplaceAll(newTable.columnDefault, "(N", ""), ")", "") //去掉默认值中的左右括号，如( 'user' )
				}
			} else if newTable.dataType == "int" || newTable.dataType == "bigint" || newTable.dataType == "smallint" || newTable.dataType == "tinyint" {
				newTable.destDefault = "default " + strings.ReplaceAll(strings.ReplaceAll(newTable.columnDefault, "(", ""), ")", "")
			} else if newTable.dataType == "bit" {
				newTable.columnDefault = strings.ReplaceAll(strings.ReplaceAll(newTable.columnDefault, "(", ""), ")", "")
			} else if newTable.dataType == "money" || newTable.dataType == "smallmoney" {
				newTable.columnDefault = strings.ReplaceAll(strings.ReplaceAll(newTable.columnDefault, "(", ""), ")", "")
			} else if newTable.dataType == "float" || newTable.dataType == "real" {
				newTable.columnDefault = strings.ReplaceAll(strings.ReplaceAll(newTable.columnDefault, "(", ""), ")", "")
			} else if newTable.dataType == "numeric" || newTable.dataType == "decimal" {
				// 创建正则表达式，number类型默认值包含括号的情况，如default (1.7),仅提取括号内数字部分
				re := regexp.MustCompile(`[\d.]+`)
				// 提取匹配的字符串
				newTable.columnDefault = strings.ReplaceAll(strings.ReplaceAll(newTable.columnDefault, "(", ""), ")", "")
				matches := re.FindAllString(newTable.columnDefault, -1)
				if len(matches) > 0 { //如果能匹配提取数字部分，否则就给null
					newTable.destDefault = "default " + matches[0]
				} else {
					newTable.destDefault = "default null"
				}
			} else if newTable.dataType == "smalldatetime" || newTable.dataType == "datetime2" || newTable.dataType == "datetime" || newTable.dataType == "date" || newTable.dataType == "time" {
				if upperDefault == "(GETDATE())" || upperDefault == "(SYSDATETIME())" || upperDefault == "(CURRENT_TIMESTAMP)" {
					newTable.destDefault = "default current_timestamp"
				} else {
					newTable.destDefault = "default null"
				}
				//re := regexp.MustCompile(`\w+`)
				//matches := re.FindAllString(newTable.dataType, -1)
				//if matches[0] == "timestamp" { //timestamp类型，才需要加精度值
				//	newTable.destDefault = "default current_timestamp(" + strconv.Itoa(newTable.numericScale) + ")"
				//}
			} else { // 其余默认值类型无需使用单引号包围
				newTable.columnDefault = strings.ReplaceAll(strings.ReplaceAll(newTable.columnDefault, "(", ""), ")", "")
				newTable.destDefault = fmt.Sprintf("default %s", newTable.columnDefault)
			}
		default:
			newTable.destDefault = "" // 如果没有默认值，默认值就是空字符串
		}
		// 列字段类型的处理
		switch newTable.dataType {
		//在 PostgreSql 中，NUMERIC 是 DECIMAL 的同义词
		case "numeric", "decimal": //具有固定精度和标度的精确数值，用户可以指定总的数字数（精度）和小数点右侧的数字数（标度）
			if newTable.columnDefault == "NULL" {
				newTable.destDefault = "null"
			}
			newTable.destType = "decimal(" + strconv.Itoa(newTable.numericPrecision) + "," + strconv.Itoa(newTable.numericScale) + ")"
			//if newTable.numericPrecision > 0 && newTable.numericScale > 0 { //场景1 Oracle number(m,n) -> PostgreSql decimal(m,n)
			//	newTable.destType = "decimal(" + strconv.Itoa(newTable.numericPrecision) + "," + strconv.Itoa(newTable.numericScale) + ")"
			//} else if newTable.avgColLen >= 6 { // 场景2 avg_col_len >= 6 ,Oracle number(m,0) -> PostgreSql bigint
			//	newTable.destType = "bigint"
			//} else if newTable.avgColLen < 6 {
			//	newTable.destType = "int"
			//}
		case "money", "smallmoney":
			newTable.destType = "decimal(" + strconv.Itoa(newTable.numericPrecision) + "," + strconv.Itoa(newTable.numericScale) + ")"
		case "real":
			newTable.destType = "double"
		case "varchar", "nvarchar":
			if newTable.characterMaximumLength.String == "-1" {
				newTable.destType = "longtext"
			} else {
				newTable.destType = "varchar(" + newTable.characterMaximumLength.String + ")"
				//clen, _ := strconv.Atoi(newTable.characterMaximumLength.String)
				//if clen > 2000 {
				//	newTable.destType = "longtext"
				//} else {
				//	newTable.destType = "varchar(" + newTable.characterMaximumLength.String + ")"
				//}
			}
		case "char", "nchar":
			newTable.destType = "char(" + newTable.characterMaximumLength.String + ")"
		case "varbinary":
			if newTable.characterMaximumLength.String == "-1" {
				newTable.destType = "longblob"
			} else {
				newTable.destType = "varbinary(" + newTable.characterMaximumLength.String + ")"
				//clen, _ := strconv.Atoi(newTable.characterMaximumLength.String)
				//if clen > 2000 {
				//	newTable.destType = "longblob"
				//} else {
				//	newTable.destType = "varbinary(" + newTable.characterMaximumLength.String + ")"
				//}
			}
		case "binary":
			newTable.destType = "binary(" + newTable.characterMaximumLength.String + ")"
		case "ntext", "text":
			newTable.destType = "longtext"
		case "image":
			newTable.destType = "longblob"
		case "bit":
			newTable.destType = "tinyint"
		case "uniqueidentifier":
			//newTable.destType = "binary(16)"
			newTable.destType = "char(36)"
		case "smalldatetime":
			newTable.destType = "datetime"
		case "datetime2": //yyyy-MM-dd HH:mm:ss.fffffff 精确到0.1微秒
			newTable.destType = "datetime(6)"
		case "timestamp":
			newTable.destType = "datetime"
		case "cursor": //游标类型，PostgreSql不支持
			//newTable.destType = "text"
		case "sql_variant", "table":
			newTable.destType = "json"
		case "xml":
			newTable.destType = "longtext"
		case "geometry", "geography": //空间数据，委屈用longtext试试
			newTable.destType = "longtext"
		//case "DATE":
		//	newTable.destType = "datetime"
		//case "CLOB", "NCLOB", "LONG":
		//	newTable.destType = "longtext"
		//case "BLOB":
		//	newTable.destType = "longtext"
		//case "RAW", "LONG RAW":
		//	newTable.destType = "longblob"
		// 其余类型，源库使用什么类型，目标库就使用什么类型
		default:
			newTable.destType = newTable.dataType
		}
		// 列注释,每个列字段的注释使用comment 注释的文字进行拼接
		colComment := ""
		if newTable.columnComment != "null" {
			colComment = fmt.Sprintf(" comment '%s'", newTable.columnComment)
		}
		// 在目标库创建的语句
		createTblSql += fmt.Sprintf("`%s` %s %s %s %s,", newTable.columnName, newTable.destType, newTable.destNullable, newTable.destDefault, colComment)
		if newTable.ordinalPosition == colTotal {
			createTblSql = createTblSql[:len(createTblSql)-1] + ")" // 最后一个列字段结尾去掉逗号,并且加上语句的右括号
		}
	}
	//fmt.Println(createTblSql) // 打印创建表语句
	LogOutput(logDir, "createSql", createTblSql+";")
	if !metaData {
		// 创建前先删除目标表
		dropDestTbl := "drop table if exists " + fmt.Sprintf("`") + tblName + fmt.Sprintf("`") + " cascade"
		if _, err = destDb.Exec(dropDestTbl); err != nil {
			log.Error("drop table ", tblName, " failed ", err)
		}
		// 创建表结构
		log.Info(fmt.Sprintf("%v Table total %d create table %s", time.Now().Format("2006-01-02 15:04:05.000000"), tableCount, tblName))

		if _, err = destDb.Exec(createTblSql); err != nil {
			log.Error("table ", tblName, " create failed  ", err)
			LogError(logDir, "tableCreateFailed", createTblSql, err)
			failedCount += 1
		}
	}
	<-ch
}

func (tb *Table) IdxCreate(logDir string, tableName string, ch chan struct{}, id int) {
	defer wg2.Done()
	var destIdxSql string
	// 查询索引、主键、唯一约束等信息，批量生成创建语句
	// -- 构建 SQL 语句
	//-- Exclude hypothetical indexes
	//-- 1 = Unique index, 2 = Primary key, 3 = Unique constraint
	srcTableSql := "SELECT " +
		"SQL_CMD = CASE " +
		"WHEN kc.type IS NOT NULL AND (kc.type = 'PK' OR fk.name IS NOT NULL) THEN " +
		"'ALTER TABLE `'+ t.name COLLATE Chinese_PRC_CI_AS + '` ADD CONSTRAINT `'+ i.name COLLATE Chinese_PRC_CI_AS + '` '+ " +
		"CASE WHEN kc.type = 'PK' THEN 'PRIMARY KEY (' ELSE 'FOREIGN KEY (' END + " +
		"col_list.PrimaryKeyColumns + ');' " +
		"ELSE " +
		"'CREATE ' + " +
		"CASE " +
		"WHEN i.is_unique = 1 THEN 'UNIQUE ' " +
		//"WHEN i.type_desc <> 'HEAP' THEN i.type_desc + ' ' " +
		"ELSE '' " +
		"END + " +
		"'INDEX `'+ i.name COLLATE Chinese_PRC_CI_AS + '` ON `'+ t.name COLLATE Chinese_PRC_CI_AS+ '`(' + col_list.IndexColumns + ');' " +
		"END " +
		"FROM " +
		"sys.tables t " +
		"JOIN " +
		"sys.indexes i ON t.object_id = i.object_id " +
		"LEFT JOIN " +
		"sys.key_constraints kc ON i.object_id = kc.parent_object_id AND i.index_id = kc.unique_index_id " +
		"LEFT JOIN " +
		"sys.foreign_keys fk ON i.object_id = fk.referenced_object_id AND i.name = fk.name COLLATE Chinese_PRC_CI_AS " +
		"CROSS APPLY ( " +
		"SELECT " +
		"PrimaryKeyColumns = STUFF(( " +
		"SELECT ', `'+ c.name + '`' " +
		"FROM sys.index_columns ic " +
		"JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id " +
		"WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id " +
		"ORDER BY ic.key_ordinal " +
		"FOR XML PATH(''), TYPE " +
		").value('.', 'NVARCHAR(MAX)'), 1, 2, ''), " +
		"IndexColumns = STUFF(( " +
		"SELECT ', `'+ c.name + '`' " +
		"FROM sys.index_columns ic " +
		"JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id " +
		"WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id " +
		"ORDER BY ic.key_ordinal " +
		"FOR XML PATH(''), TYPE " +
		").value('.', 'NVARCHAR(MAX)'), 1, 2, '') " +
		") AS col_list " +
		"WHERE " +
		"i.type_desc <> 'FUNCTION-BASED' AND t.name = '%s' COLLATE Chinese_PRC_CI_AS"
	//sqlStr := fmt.Sprintf("SELECT (CASE WHEN C.CONSTRAINT_TYPE = 'P' OR C.CONSTRAINT_TYPE = 'R' THEN 'ALTER TABLE `' || T.TABLE_NAME || '` ADD CONSTRAINT ' ||'`'||T.INDEX_NAME||'`' || (CASE WHEN C.CONSTRAINT_TYPE = 'P' THEN ' PRIMARY KEY (' ELSE ' FOREIGN KEY (' END) || listagg(T.COLUMN_NAME,',') within group(order by T.COLUMN_position) || ');' ELSE 'CREATE ' || (CASE WHEN I.UNIQUENESS = 'UNIQUE' THEN I.UNIQUENESS || ' ' ELSE CASE WHEN I.INDEX_TYPE = 'NORMAL' THEN '' ELSE  I.INDEX_TYPE || ' '  END END) || 'INDEX ' || '`'||T.INDEX_NAME||'`' || ' ON ' || T.TABLE_NAME || '(' ||listagg(T.COLUMN_NAME,',') within group(order by T.COLUMN_position) || ');' END) SQL_CMD FROM USER_IND_COLUMNS T, USER_INDEXES I, USER_CONSTRAINTS C WHERE T.INDEX_NAME = I.INDEX_NAME  AND T.INDEX_NAME = C.CONSTRAINT_NAME(+)  and i.index_type != 'FUNCTION-BASED NORMAL' and i.table_name='%s' GROUP BY T.TABLE_NAME, T.INDEX_NAME, I.UNIQUENESS, I.INDEX_TYPE,C.CONSTRAINT_TYPE", tableName)
	sqlStr := fmt.Sprintf(srcTableSql, tableName)
	rows, err := srcDb.Query(sqlStr)
	if err != nil {
		log.Error(err, sqlStr)
	}
	defer rows.Close()
	// 从sql结果集遍历，获取到创建语句
	for rows.Next() {
		var destSql sql.NullString
		if err := rows.Scan(&destSql); err != nil {
			log.Error(err)
		}
		if destSql.String == "" {
			continue
		}
		LogOutput(logDir, "createSql", destSql.String)
		destIdxSql = "/* goapp idx */" + destSql.String
		// 创建目标索引，主键、其余约束
		if !metaData {
			if _, err = destDb.Exec(destIdxSql); err != nil {
				log.Error("index ", destIdxSql, " create index failed ", err)
				LogError(logDir, "idxCreateFailed", destIdxSql, err)
				failedCount += 1
			}
		}
	}
	if destIdxSql != "" {
		log.Info("[", id, "] Table ", tableName, " create index finish ")
	}
	<-ch
}

func (tb *Table) SeqCreate(logDir string) (ret []string) {
	startTime := time.Now()
	failedCount = 0
	var colName, tableName, isIdentity, seedValue, incrementValue, colmunType string
	srcTableSql := `SELECT 
    c.TABLE_NAME,
    c.COLUMN_NAME,
    ic.is_identity,
    ic.seed_value,
    ic.increment_value,
    c.DATA_TYPE
FROM 
    INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN 
    sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.name
LEFT JOIN 
    sys.identity_columns ic ON sc.column_id = ic.column_id AND sc.object_id = ic.object_id
WHERE 
    ic.is_identity = 1 and ic.seed_value is not null and ic.increment_value is not null`
	if len(excludeTab) > 0 {
		buffer := bytes.NewBufferString(" and c.TABLE_NAME not in ( ")
		for index, tabName := range excludeTab {
			if index < len(excludeTab)-1 {
				buffer.WriteString("'" + tabName + "'" + ",")
			} else {
				buffer.WriteString("'" + tabName + "'" + ")")
			}
		}
		srcTableSql = srcTableSql + buffer.String()
	}
	//rows, err := srcDb.Query("select table_name,trigger_body from user_triggers where upper(trigger_type) ='BEFORE EACH ROW'")
	rows, err := srcDb.Query(srcTableSql)
	if err != nil {
		log.Error(err)
	}
	defer rows.Close()
	idx := 0
	for rows.Next() {
		idx += 1
		err := rows.Scan(&tableName, &colName, &isIdentity, &seedValue, &incrementValue, &colmunType)
		if err != nil {
			log.Error(err)
		}
		//-- 将 id 字段设置为自增类型
		//ALTER TABLE example_table 	MODIFY COLUMN id INT AUTO_INCREMENT;
		// 创建目标数据库该表表的自增列索引
		sqlAutoColIdx := "/* goapp seq ids*/" + "create index ids_" + tableName + "_" + colName + "_" + strconv.Itoa(idx) + " on " + tableName + "(" + colName + ")"
		//sqlAutoColIdx:=fmt.Sprintf("/* goapp seq ids*/ ALTER TABLE %s MODIFY COLUMN %s bigint AUTO_INCREMENT")
		log.Info("[", idx, "] create auto_increment for table ", tableName)
		LogOutput(logDir, "createSql", sqlAutoColIdx+";")
		if !metaData {
			if _, err = destDb.Exec(sqlAutoColIdx); err != nil {
				log.Error(sqlAutoColIdx, " create index autoCol failed ", err)
				LogError(logDir, "AutoIdxCreateFailed", sqlAutoColIdx, err)
				failedCount += 1
			}
		}
		// 更改目标数据库该表的列属性为自增列
		//sqlModifyAuto := "/* goapp seq auto */" + "alter table " + tableName + " modify " + colName + " bigint auto_increment"
		sqlModifyAuto := fmt.Sprintf("/* goapp seq auto */alter table %s modify %s %s auto_increment", tableName, colName, colmunType)
		LogOutput(logDir, "createSql", sqlModifyAuto+";")
		if !metaData {
			if _, err = destDb.Exec(sqlModifyAuto); err != nil {
				log.Error(sqlModifyAuto, " failed ", err)
				LogError(logDir, "alterTableFailed", sqlModifyAuto, err)
				failedCount += 1
			}
		}
		//dbRet = strings.ToUpper(dbRet)
		//dbRet = strings.ReplaceAll(dbRet, "INTO:", "INTO :")
		//dbRet = strings.ReplaceAll(dbRet, "SYS.DUAL ", "DUAL")
		//dbRet = strings.ReplaceAll(dbRet, "SYS.DUAL", "DUAL")
		//dbRet = strings.ReplaceAll(dbRet, "\n", "")
		//pattern := `SELECT\s+(.*?)\.NEXTVAL\s+INTO\s+:NEW\.`
		//re := regexp.MustCompile(pattern)
		//match := re.FindStringSubmatch(dbRet)
		//if len(match) > 0 { // 第一层，先正则匹配SELECT .NEXTVAL INTO :NEW包含的字符窜,主要是要匹配到自增列性质的触发器
		//	//如果符合第一层正则的条件，再匹配第二层，第二层主要是获取:NEW.后面的名称，即自增列名称
		//	re := regexp.MustCompile(`:NEW\.(\w+)`) // 正则表达式，匹配以 ":NEW." 开头的字符串，并提取后面的单词字符（包括字母、数字和下划线）
		//	match := re.FindStringSubmatch(dbRet)   // 查找匹配项
		//	if len(match) == 2 {
		//		autoColName := match[1]
		//		// 创建目标数据库该表表的自增列索引
		//		sqlAutoColIdx := "/* goapp */" + "create index ids_" + tableName + "_" + autoColName + "_" + strconv.Itoa(idx) + " on " + tableName + "(" + autoColName + ")"
		//		log.Info("[", idx, "] create auto_increment for table ", tableName)
		//		if _, err = destDb.Exec(sqlAutoColIdx); err != nil {
		//			log.Error(sqlAutoColIdx, " create index autoCol failed ", err)
		//			LogError(logDir, "AutoIdxCreateFailed", sqlAutoColIdx, err)
		//			failedCount += 1
		//		}
		//		// 更改目标数据库该表的列属性为自增列
		//		sqlModifyAuto := "/* goapp */" + "alter table " + tableName + " modify " + autoColName + " bigint auto_increment"
		//		if _, err = destDb.Exec(sqlModifyAuto); err != nil {
		//			log.Error(sqlModifyAuto, " failed ", err)
		//			LogError(logDir, "alterTableFailed", sqlModifyAuto, err)
		//			failedCount += 1
		//		}
		//	}
		//}
	}
	endTime := time.Now()
	cost := time.Since(startTime)
	ret = append(ret, "AutoIncrement", startTime.Format("2006-01-02 15:04:05.000000"), endTime.Format("2006-01-02 15:04:05.000000"), strconv.Itoa(failedCount), cost.String())
	return ret
}

func (tb *Table) FkCreate(logDir string) (ret []string) {
	startTime := time.Now()
	failedCount = 0
	var tableName, sqlStr string
	srcTableSql := `SELECT 
    KCU1.TABLE_NAME,
    'ALTER TABLE ' + KCU1.TABLE_NAME + ' ADD CONSTRAINT ' + TC1.CONSTRAINT_NAME + ' FOREIGN KEY (' + 
        STUFF(
            (SELECT ',' + KCU2.COLUMN_NAME 
             FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
             WHERE KCU2.CONSTRAINT_NAME = TC1.CONSTRAINT_NAME 
             AND KCU2.TABLE_SCHEMA = KCU1.TABLE_SCHEMA
             ORDER BY KCU2.ORDINAL_POSITION 
             FOR XML PATH('')), 1, 1, ''
        ) + ') REFERENCES ' + 
        KCU2.TABLE_NAME + '(' + 
        STUFF(
            (SELECT ',' + KCU3.COLUMN_NAME 
             FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU3 
             WHERE KCU3.CONSTRAINT_NAME = TC2.CONSTRAINT_NAME 
             AND KCU3.TABLE_SCHEMA = KCU2.TABLE_SCHEMA
             ORDER BY KCU3.ORDINAL_POSITION 
             FOR XML PATH('')), 1, 1, ''
        ) + ');' AS AlterTableStatement
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC1
JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
    ON TC1.CONSTRAINT_NAME = KCU1.CONSTRAINT_NAME 
    AND TC1.TABLE_NAME = KCU1.TABLE_NAME 
    AND TC1.TABLE_SCHEMA = KCU1.TABLE_SCHEMA
JOIN 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
    ON TC1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 
    ON RC.UNIQUE_CONSTRAINT_NAME = TC2.CONSTRAINT_NAME 
JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
    ON TC2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
    AND TC2.TABLE_NAME = KCU2.TABLE_NAME 
    AND TC2.TABLE_SCHEMA = KCU2.TABLE_SCHEMA
WHERE 
    TC1.CONSTRAINT_TYPE = 'FOREIGN KEY' %s
ORDER BY 
    KCU1.TABLE_NAME, TC1.CONSTRAINT_NAME;
`
	buffer := bytes.NewBufferString("  ")
	if len(excludeTab) > 0 {
		buffer.WriteString(" and KCU1.TABLE_NAME not in ( ")
		for index, tabName := range excludeTab {
			if index < len(excludeTab)-1 {
				buffer.WriteString("'" + tabName + "'" + ",")
			} else {
				buffer.WriteString("'" + tabName + "'" + ")")
			}
		}
	}
	//rows, err := srcDb.Query("SELECT B.TABLE_NAME,'ALTER TABLE ' || B.TABLE_NAME || ' ADD CONSTRAINT ' ||\n       B.CONSTRAINT_NAME || ' FOREIGN KEY (' ||\n       (SELECT listagg(A.COLUMN_NAME,',') within group(order by a.position)\n        FROM USER_CONS_COLUMNS A\n        WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) || ') REFERENCES ' ||\n       (SELECT B1.table_name FROM USER_CONSTRAINTS B1\n        WHERE B1.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME) || '(' ||\n       (SELECT listagg(A.COLUMN_NAME,',') within group(order by a.position)\n        FROM USER_CONS_COLUMNS A\n        WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME) || ');'\nFROM USER_CONSTRAINTS B\nWHERE B.CONSTRAINT_TYPE = 'R' ")
	sql := fmt.Sprintf(srcTableSql, buffer.String())
	rows, err := srcDb.Query(sql)
	if err != nil {
		log.Error(err)
	}
	defer rows.Close()
	idx := 0
	for rows.Next() {
		idx += 1
		err := rows.Scan(&tableName, &sqlStr)
		if err != nil {
			log.Error(err)
		}
		log.Info("[", idx, "] create foreign key for table ", tableName)
		sqlStr = "/* goapp fk */" + sqlStr
		LogOutput(logDir, "createSql", sqlStr)
		if !metaData {
			if _, err = destDb.Exec(sqlStr); err != nil {
				log.Error(sqlStr, " create foreign key failed ", err)
				LogError(logDir, "FKCreateFailed", sqlStr, err)
				failedCount += 1
			}
		}
	}
	endTime := time.Now()
	cost := time.Since(startTime)
	ret = append(ret, "ForeignKey", startTime.Format("2006-01-02 15:04:05.000000"), endTime.Format("2006-01-02 15:04:05.000000"), strconv.Itoa(failedCount), cost.String())
	return ret
}

func (tb *Table) NormalIdx(logDir string) (ret []string) {
	startTime := time.Now()
	failedCount = 0
	var idxName, tableName, sqlStr, createSql string
	//err := srcDb.QueryRow("select user from dual").Scan(&userName)
	//err := srcDb.QueryRow("select current_user").Scan(&userName)
	//if err != nil {
	//	log.Error(err)
	//}
	srcTableSql := `SELECT 
    i.name AS index_name,
    o.name AS table_name
FROM 
    sys.indexes i
INNER JOIN 
    sys.objects o ON i.object_id = o.object_id
WHERE 
    o.type = 'U'
    AND i.type_desc <> 'HEAP'
    AND i.is_primary_key <> 1 and i.is_unique <>1
ORDER BY 
    o.name, i.name;
`
	//rows, err := srcDb.Query("Select index_name,table_name from user_indexes where index_type='FUNCTION-BASED NORMAL'")
	sql := fmt.Sprintf(srcTableSql)
	rows, err := srcDb.Query(sql)
	if err != nil {
		log.Error(err)
	}
	defer rows.Close()
	idx := 0
	for rows.Next() {
		idx += 1
		err := rows.Scan(&idxName, &tableName) // 先获取normal-index的索引名称和表名
		if err != nil {
			log.Error(err)
		}
		if len(idxName) > 0 { // 如果有normal-index，就通过dbms_metadata获取该normal-index的DDL语句
			//o.name='tablename',name='indexname'
			srcTableSql = `SELECT 
    STUFF(
        (SELECT ', ' + c.name
         FROM sys.index_columns ic
         JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
         FOR XML PATH('')), 1, 2, ''
    ) AS IndexColumns
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.name = '%s' AND i.name = '%s';
`
			//sqlStr = fmt.Sprintf(srcTableSql, idxName, userName, idxName, userName, userName)
			sqlStr = fmt.Sprintf(srcTableSql, tableName, idxName)
			err := srcDb.QueryRow(sqlStr).Scan(&createSql) // 获取到创建normal-index的sql语句
			if err != nil {
				log.Error(err)
			}
			if createSql == "" {
				continue
			}
			createSql = fmt.Sprintf("CREATE INDEX %s ON %s (%s)", idxName, tableName, createSql)
			log.Info("[", idx, "] create normal index for table ", tableName)
			LogOutput(logDir, "createSql", createSql+";")
			createSql = "/* goapp normal index */" + createSql
			if !metaData {
				if _, err = destDb.Exec(createSql); err != nil {
					log.Error(createSql, " create normal index failed ", err)
					LogError(logDir, "NormalIdxCreateFailed", createSql, err)
					failedCount += 1
				}
			}
		}

	}
	endTime := time.Now()
	cost := time.Since(startTime)
	ret = append(ret, "NormalIndex", startTime.Format("2006-01-02 15:04:05.000000"), endTime.Format("2006-01-02 15:04:05.000000"), strconv.Itoa(failedCount), cost.String())
	return ret
}

func (tb *Table) CommentCreate(logDir string) (ret []string) {
	startTime := time.Now()
	failedCount = 0
	var tableName, tableComment, createSql string
	//-- 获取表的注释
	//AND obj.name = 'YourTableName' -- 替换为你的表名
	srcTableSql := `SELECT 
    obj.name AS TABLE_NAME,
    ep.value AS TABLE_COMMENT
FROM 
    sys.objects obj
LEFT JOIN 
    sys.extended_properties ep ON ep.major_id = obj.object_id 
                             AND ep.minor_id = 0 
                             AND ep.name = 'MS_Description'
WHERE 
    obj.type = 'U'
    AND ep.value is not null
`
	//检查表的特定字段是否有注释：
	//-- 替换为你的表名
	//-- 替换为你的列名
	//	srcColumnSql := `SELECT
	//    col.name AS ColumnName,
	//    ep.value AS ColumnComment
	//FROM
	//    sys.columns col
	//LEFT JOIN
	//    sys.extended_properties ep ON ep.major_id = col.object_id
	//                             AND ep.minor_id = col.column_id
	//                             AND ep.name = 'MS_Description'
	//WHERE
	//    col.object_id = OBJECT_ID('%s')
	//    AND col.name = '%s'
	//`
	//rows, err := srcDb.Query("select TABLE_NAME,'alter table '||TABLE_NAME||' comment '||''''||COMMENTS||'''' as create_comment  from USER_TAB_COMMENTS where COMMENTS is not null")
	rows, err := srcDb.Query(srcTableSql)
	if err != nil {
		log.Error(err)
	}
	defer rows.Close()
	idx := 0
	for rows.Next() {
		idx += 1
		err := rows.Scan(&tableName, &tableComment) // 先获取normal-index的索引名称和表名
		if err != nil {
			log.Error(err)
		}
		if len(tableComment) > 0 { // 如果有normal-index，就通过dbms_metadata获取该normal-index的DDL语句
			createSql = "alter table %s comment '%s'" //修改表注释 ALTER TABLE tb_user COMMENT '用户信息表2';
			createSql = fmt.Sprintf(createSql, tableName, tableComment)
			log.Info("[", idx, "] create comment for table ", tableName)
			LogOutput(logDir, "createSql", createSql+";")
			if !metaData {
				if _, err = destDb.Exec(createSql); err != nil {
					log.Error(createSql, " create comment failed ", err)
					LogError(logDir, "CommentCreateFailed", createSql, err)
					failedCount += 1
				}
			}
		}

	}
	endTime := time.Now()
	cost := time.Since(startTime)
	ret = append(ret, "Comment", startTime.Format("2006-01-02 15:04:05.000000"), endTime.Format("2006-01-02 15:04:05.000000"), strconv.Itoa(failedCount), cost.String())
	return ret
}

// 视图只输出到日志
func (tb *Table) ViewCreate(logDir string) (ret []string) {
	startTime := time.Now()
	failedCount = 0
	var viewName string
	var dbRet sql.NullString
	srcTableSql := `SELECT 
    v.name AS view_name,
    m.definition AS text
FROM 
    sys.views v
INNER JOIN 
    sys.sql_modules m ON v.object_id = m.object_id
`
	//rows, err := srcDb.Query("select view_name,text from user_views")
	rows, err := srcDb.Query(srcTableSql)
	if err != nil {
		log.Error(err)
	}
	defer rows.Close()
	idx := 0
	for rows.Next() {
		idx += 1
		err := rows.Scan(&viewName, &dbRet)
		if err != nil {
			log.Error(err)
		}
		log.Info("[", idx, "] create view ", viewName)
		if dbRet.Valid && dbRet.String != "" {
			LogOutput(logDir, "createViewSql", "--视图名称：--"+viewName+" \n"+dbRet.String+";")
		}
		//if _, err = srcDb.Exec("alter view " + viewName + " compile"); err != nil { // 先编译下源数据库的视图
		//	log.Error(" alter view ", viewName, " compile failed", err)
		//}
		//dbRet = strings.ToUpper(dbRet)
		//dbRet = strings.ReplaceAll(dbRet, "--", "-- -- ")
		//dbRet = strings.ReplaceAll(dbRet, "[", "`")
		//dbRet = strings.ReplaceAll(dbRet, "]", "`")
		//dbRet = strings.ReplaceAll(dbRet, "ISNULL(", "IFNULL(")
		//dbRet = strings.ReplaceAll(dbRet, "NCHAR('\0030')", "0")
		//dbRet = strings.ReplaceAll(dbRet, "NCHAR('\0031')", "1")
		//dbRet = strings.ReplaceAll(dbRet, "NCHAR('\0033')", "3")
		//if len(viewName) > 0 {
		//	sqlStr := "create or replace view " + viewName + " as " + dbRet
		//	log.Info("[", idx, "] create view ", viewName)
		//	LogOutput(logDir, "createSql", sqlStr+";")
		//	//if !metaData {
		//	//	if _, err = destDb.Exec(sqlStr); err != nil {
		//	//		LogError(logDir, "ViewCreateFailed", sqlStr, err)
		//	//		failedCount += 1
		//	//	}
		//	//}
		//}
	}
	endTime := time.Now()
	cost := time.Since(startTime)
	ret = append(ret, "View", startTime.Format("2006-01-02 15:04:05.000000"), endTime.Format("2006-01-02 15:04:05.000000"), strconv.Itoa(failedCount), cost.String())
	return ret
}

func (tb *Table) PrintDbFunc(logDir string) { //转储源数据库的函数、存储过程、包等对象到平面文件
	var dbRet string
	//-- 分别代表标量函数、内联表值函数、表值函数、存储过程、CLR 存储过程
	//#    o.type_desc AS OBJECT_TYPE,
	//     o.name AS OBJECT_NAME,
	srcTableSql := `SELECT
    m.definition AS DDL_SQL
FROM
    sys.objects o
INNER JOIN
    sys.sql_modules m ON o.object_id = m.object_id
WHERE
    o.type IN ('FN', 'IF', 'TF', 'P', 'PC') 
ORDER BY
    o.type_desc
`
	//rows, err := srcDb.Query("SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name) ddl_sql FROM USER_OBJECTS u where U.OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') order by OBJECT_TYPE")
	rows, err := srcDb.Query(srcTableSql)
	if err != nil {
		log.Error(err)
	}
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&dbRet)
		if err != nil {
			log.Error(err)
		}
		LogError(logDir, "FuncObject", dbRet, err)
	}
}
